﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_MoneyReturns : IDAL_FIM_MoneyReturns
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_FIM_MoneyReturns()
        {

        }

        public IList<M_FIM_MoneyReturns> GetFull(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq
            , Nullable<int> contact_person_seq, Nullable<int> emp_seq, Nullable<int> money_req_seq
            , bool bIsFinance)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyReturns> infoList = new List<M_FIM_MoneyReturns>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@contact_person_seq", contact_person_seq);
                dbManager.AddParameters(5, "@emp_seq", emp_seq);
                dbManager.AddParameters(6, "@money_req_seq", money_req_seq);

                strSQL = "SELECT re.return_seq, re.return_date, re.ref_no, re.money_req_seq"
                    + ", re.approved_by, re.approved_when, re.tran_grp_seq "
                    + ", mr.ref_no as money_req_ref_no, mr.money_req_name, mr.bgm_source_seq"
                    + ", s.bgm_source_name, mr.bgm_year, mr.money_req_date, mr.use_advance"
                    //+ ", advance_bgm_source_seq"
                    + ", mr.borrow_no, mr.debtor_emp_seq, mr.debtor_contact_seq"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as debtor_emp_name"
                    + ", c.NameTh as debtor_contact_name"
                    + ", case when debtor_emp_seq is not null then concat(np.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " else c.NameTh"
                        + " end as debtor_name"
                    + ", coalesce(red.total_bath_amount, 0) as total_bath_amount"
                    + ", coalesce(ramount.receive_amount, 0) as receive_bath_amount"
                    + ", coalesce(mrd.money_req_total_bath_amount, 0) as money_req_total_bath_amount"
                    + ", coalesce(mrd.money_req_total_bath_amount, 0)"
                        + " - coalesce(reamount.return_amount, 0)"
                        + " - coalesce(ramount.receive_amount, 0) as remain_bath_amount"

                    + ", re.record_status, rs.record_status_name"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"

                    + " FROM fim_money_returns re"
                    + " LEFT JOIN (select red.return_seq"
                        + ", sum(coalesce(red.amount, 0)) as total_bath_amount"
                        + " from fim_money_return_dtls red"
                        + " where red.record_status not like 'D'"
                        + " group by red.return_seq"
                        + ") red ON re.return_seq = red.return_seq"
                    + " LEFT JOIN fim_money_requests mr ON re.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.amount, 0)) as money_req_total_bath_amount"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN (select re.money_req_seq"
                        + ", sum(coalesce(red.amount, 0)) as return_amount"
                        + " from fim_money_returns re"
                        + " left join fim_money_return_dtls red on re.return_seq = red.return_seq"
                        + " where re.record_status not like 'D'"
                        + " and red.record_status not like 'D'"
                        + " group by re.money_req_seq"
                        + ") reamount ON mr.money_req_seq = reamount.money_req_seq"
                    + " LEFT JOIN (select r.money_req_seq"
                        + ", sum(coalesce(r.amount, 0)) as receive_amount"
                        + " from fim_receives r"
                        + " where r.record_status not like 'D'"
                        + " group by r.money_req_seq"
                        + ") ramount ON mr.money_req_seq = ramount.money_req_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON mr.debtor_contact_seq = cp.contact_person_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN sys_record_status rs ON re.record_status = rs.record_status"
                    + (bIsFinance ? " WHERE re.record_status in ('A', 'P')" : " WHERE re.record_status not like 'D'")
                    + (money_req_seq != null ? " AND re.money_req_seq = @money_req_seq" : "")
                     + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                    + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                    + (contact_person_seq != null ? " AND mr.contact_person_seq = @contact_person_seq" : "")
                    + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyReturns info = new M_FIM_MoneyReturns();
                        info.return_seq = Convert.ToInt32(drc[i]["return_seq"]);
                        info.return_date = cn.ConvertNullDateTime(drc[i]["return_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        
                        info.money_req_ref_no = drc[i]["money_req_ref_no"].ToString();
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.money_req_date = cn.ConvertNullDateTime(drc[i]["money_req_date"]);
                        info.use_advance = drc[i]["use_advance"].ToString();
                        info.borrow_no = drc[i]["borrow_no"].ToString();
                        info.debtor_emp_seq = cn.ConvertNullInt(drc[i]["debtor_emp_seq"]);
                        info.debtor_contact_seq = cn.ConvertNullInt(drc[i]["debtor_contact_seq"]);
                        info.debtor_emp_name = drc[i]["debtor_emp_name"].ToString();
                        info.debtor_contact_name = drc[i]["debtor_contact_name"].ToString();
                        info.debtor_name = drc[i]["debtor_name"].ToString();
                        info.total_bath_amount = cn.ConvertNullDouble(drc[i]["total_bath_amount"]);
                        info.money_req_total_bath_amount = cn.ConvertNullDouble(drc[i]["money_req_total_bath_amount"]);
                        info.receive_bath_amount = cn.ConvertNullDouble(drc[i]["receive_bath_amount"]);
                        info.remain_bath_amount = cn.ConvertNullDouble(drc[i]["remain_bath_amount"]);
                
                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyReturns> GetList(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq
            , Nullable<int> contact_person_seq, Nullable<int> emp_seq, string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyReturns> infoList = new List<M_FIM_MoneyReturns>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@contact_person_seq", contact_person_seq);
                dbManager.AddParameters(5, "@emp_seq", emp_seq);
                dbManager.AddParameters(6, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    + " FROM( SELECT re.return_seq, concat(re.ref_no, ' : รับคืนเงินจากสัญญายืมเงินที่', mr.borrow_no) as return_name"
                        + " FROM fim_money_returns re"
                        + " LEFT JOIN fim_money_requests mr ON re.money_req_seq = mr.money_req_seq"
                        + " WHERE re.record_status not like 'D'"
                        + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                        + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                        + (contact_person_seq != null ? " AND mr.contact_person_seq = @contact_person_seq" : "")
                        + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "")
                        + ") re"
                    + (filter != null ? " WHERE re.return_name like @filter" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyReturns info = new M_FIM_MoneyReturns();
                        info.return_seq = Convert.ToInt32(drc[i]["return_seq"]);
                        info.return_name = drc[i]["return_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MoneyReturns GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyReturns info = new M_FIM_MoneyReturns();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT re.return_seq, re.return_date, re.ref_no, re.money_req_seq"
                    + ", re.approved_by, re.approved_when, re.tran_grp_seq"
                    + ", mr.ref_no as money_req_ref_no, mr.money_req_name, mr.bgm_source_seq"
                    + ", s.bgm_source_name, mr.bgm_year, mr.money_req_date, mr.use_advance"
                    //+ ", advance_bgm_source_seq"
                    + ", mr.borrow_no, mr.debtor_emp_seq, mr.debtor_contact_seq"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as debtor_emp_name"
                    + ", c.NameTh as debtor_contact_name"
                    + ", case when debtor_emp_seq is not null then concat(np.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " else c.NameTh"
                        + " end as debtor_name"
                    + ", coalesce(red.total_bath_amount, 0) as total_bath_amount"
                    + ", coalesce(red2.total_bath_amount, 0) as total_bath_amount_accum"
                    + ", coalesce(ramount.receive_amount, 0) as receive_bath_amount"
                    + ", coalesce(mrd.money_req_total_bath_amount, 0) as money_req_total_bath_amount"
                    + ", coalesce(mrd.money_req_total_bath_amount, 0)"
                        + " - coalesce(red2.total_bath_amount, 0)"
                        + " - coalesce(ramount.receive_amount, 0) as remain_bath_amount"

                    + ", re.record_status, rs.record_status_name"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"

                    + " FROM fim_money_returns re"
                    + " LEFT JOIN (select red.return_seq"
                        + ", sum(coalesce(red.amount, 0)) as total_bath_amount"
                        + " from fim_money_return_dtls red"
                        + " where red.record_status not like 'D'"
                        + " group by red.return_seq"
                        + ") red ON re.return_seq = red.return_seq"
                    + " LEFT JOIN (select re.money_req_seq"
                        + ", sum(coalesce(red.amount, 0)) as total_bath_amount"
                        + " from fim_money_return_dtls red"
                        + " left join fim_money_returns re on red.return_seq = re.return_seq"
                        + " where red.record_status not like 'D'"
                        + " and re.record_status not like 'D'"
                        + " group by re.money_req_seq"
                        + ") red2 ON re.money_req_seq = red2.money_req_seq"
                    + " LEFT JOIN fim_money_requests mr ON re.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.amount, 0)) as money_req_total_bath_amount"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    //+ " LEFT JOIN (select re.money_req_seq"
                    //    + ", sum(coalesce(red.amount, 0)) as return_amount"
                    //    + " from fim_money_returns re"
                    //    + " left join fim_money_return_dtls red on re.return_seq = red.return_seq"
                    //    + " where re.record_status not like 'D'"
                    //    + " and red.record_status not like 'D'"
                    //    + " group by re.money_req_seq"
                    //    + ") reamount ON mr.money_req_seq = reamount.money_req_seq"
                    + " LEFT JOIN (select r.money_req_seq"
                        + ", sum(coalesce(r.amount, 0)) as receive_amount"
                        + " from fim_receives r"
                        + " where r.record_status not like 'D'"
                        + " group by r.money_req_seq"
                        + ") ramount ON mr.money_req_seq = ramount.money_req_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON mr.debtor_contact_seq = cp.contact_person_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN sys_record_status rs ON re.record_status = rs.record_status"
                    + " WHERE re.return_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.return_seq = Convert.ToInt32(drc[0]["return_seq"]);
                    info.return_date = cn.ConvertNullDateTime(drc[0]["return_date"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.money_req_seq = cn.ConvertNullInt(drc[0]["money_req_seq"]);

                    info.money_req_ref_no = drc[0]["money_req_ref_no"].ToString();
                    info.money_req_name = drc[0]["money_req_name"].ToString();
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.money_req_date = cn.ConvertNullDateTime(drc[0]["money_req_date"]);
                    info.use_advance = drc[0]["use_advance"].ToString();
                    info.borrow_no = drc[0]["borrow_no"].ToString();
                    info.debtor_emp_seq = cn.ConvertNullInt(drc[0]["debtor_emp_seq"]);
                    info.debtor_contact_seq = cn.ConvertNullInt(drc[0]["debtor_contact_seq"]);
                    info.debtor_emp_name = drc[0]["debtor_emp_name"].ToString();
                    info.debtor_contact_name = drc[0]["debtor_contact_name"].ToString();
                    info.debtor_name = drc[0]["debtor_name"].ToString();
                    info.total_bath_amount = cn.ConvertNullDouble(drc[0]["total_bath_amount"]);
                    info.money_req_total_bath_amount = cn.ConvertNullDouble(drc[0]["money_req_total_bath_amount"]);
                    info.receive_bath_amount = cn.ConvertNullDouble(drc[0]["receive_bath_amount"]);
                    info.remain_bath_amount = cn.ConvertNullDouble(drc[0]["remain_bath_amount"]);
                    info.total_bath_amount_accum = cn.ConvertNullDouble(drc[0]["total_bath_amount_accum"]);

                    info.approved_by = cn.ConvertNullInt(drc[0]["approved_by"]);
                    info.approved_when = cn.ConvertNullDateTime(drc[0]["approved_when"]);
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq from fim_money_requests";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_MoneyReturns info)
        {
            string strSQL;
            string strSeq = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@return_seq", info.return_seq);
                dbManager.AddParameters(1, "@return_date", info.return_date);
                dbManager.AddParameters(2, "@ref_no", info.ref_no);
                dbManager.AddParameters(3, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(4, "@remarks", info.remarks);

                dbManager.AddParameters(5, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(6, "@created_by", info.created_by);
                dbManager.AddParameters(7, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_money_returns"
                    + "(return_date, ref_no, money_req_seq, remarks"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@return_date"
                        + ", @ref_no"
                        + ", @money_req_seq"
                        + ", @remarks"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when);"
                    + " select scope_identity()";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                dbManager.CommitTransaction();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MoneyReturns info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@return_seq", info.return_seq);
                dbManager.AddParameters(1, "@return_date", info.return_date);
                dbManager.AddParameters(2, "@ref_no", info.ref_no);
                dbManager.AddParameters(3, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(4, "@remarks", info.remarks);

                dbManager.AddParameters(5, "@updated_by", info.updated_by);
                dbManager.AddParameters(6, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_returns"
                    + " set return_date = @return_date"
                    + ", ref_no = @ref_no"
                    + ", money_req_seq = @money_req_seq"
                    + ", remarks = @remarks"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE return_seq = @return_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangeStatus(int return_seq, string record_status, int emp_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@return_seq", return_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", emp_seq);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE fim_money_returns"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE return_seq = @return_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MoneyReturns info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@return_seq", info.return_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_returns"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE return_seq = @return_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE fim_money_return_dtls"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE return_seq = @return_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateGlTranSeq(M_FIM_MoneyReturns info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@return_seq", info.return_seq);
                dbManager.AddParameters(1, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_returns"
                    + " SET tran_grp_seq = @tran_grp_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE return_seq = @return_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Report_ReceiveVoucher(int seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.bgm_year, r.receive_type_seq, r.receipt_no"
                    + ", DATEADD(year, 543, r.receive_date) as receive_date, r.manual_receipt"
                    + ", r.receive_mode, r.method_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.contact_seq, '') not like '' then c.NameTh"
                        + " else case when coalesce(r.emp_seq, '') not like '' then concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " else r.temp_contact"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"

                    + ", s.bgm_source_name, c.NameTh as contact_name, m.method_name"//, cur.currency_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    + ", sum(coalesce(amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    //+ " LEFT JOIN fim_mas_currencies cur ON r.currency_seq = cur.currency_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON r.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.receive_seq = @seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable LoadMoneyReq()
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT money_req_seq, money_req_name FROM fim_money_requests"
                         + " WHERE record_status not LIKE @status ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable LoadContact(Nullable<int> money_req_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = " SELECT ps.emp_seq ,concat(np.prefix_name,' ', ps.first_name,' ',ps.last_name) as emp_name "
                        +" FROM hrm_ps_employees ps "
                        +" LEFT JOIN mas_name_prefixes np ON ps.name_prefix_seq = np.name_prefix_seq "
                        +" LEFT JOIN hrm_ps_employee_statuses es ON ps.emp_status_seq = es.emp_status_seq "
                        + " LEFT JOIN fim_money_requests mq ON ps.emp_seq = mq.emp_seq"
                        + " WHERE es.Active = 1 AND ps.record_status not LIKE 'D' AND mq.money_req_seq = @money_req_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

                dbManager.Dispose();
            }
            return dtb;
        }

        public DataTable GetReturnsFromMoneyReqSeq(int money_req_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "select *"
                    + " from ("
                        + " select re.ref_no, re.return_date as report_date, red.amount"
                        + " from fim_money_returns re"
                        + " left join (select red.return_seq"
	                        + ", sum(red.amount) as amount"
	                        + " from fim_money_return_dtls red"
	                        + " where red.record_status <> 'D'"
	                        + " group by red.return_seq"
                        + ") red on re.return_seq = red.return_seq"
                        + " where re.record_status = 'A'"
                        + " and re.money_req_seq = @money_req_seq"

                        + " union all"

                        + " select re.receipt_no, re.receive_date as report_date, re.amount"
                        + " from fim_receives re"
                        + " where re.record_status <> 'D'"
                        + " and re.receipt_no is not null"
                        + " and re.receive_mode = 1"
                        + " and re.money_req_seq = @money_req_seq"
                    + ") t"
                    + " order by report_date";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

                dbManager.Dispose();
            }
            return dtb;
        }
    }
}
